﻿using Sc.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sc.Persistence
{
    public class ReportDAO
    {
        /// <summary>
        /// 根据机构获取车辆使用次数
        /// </summary>
        /// <param name="orgz">机构</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="tag">报表类型</param>
        /// <returns></returns>
        public IList<ReportModel> GetUseCountData(int orgz, DateTime start, DateTime end, int tag)
        {
            IList<ReportModel> list = new List<ReportModel>();
            var sql = GetSql(tag);
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@Orgz",SqlDbType.Int),
                new SqlParameter("@Start",SqlDbType.DateTime),
                new SqlParameter("@End",SqlDbType.DateTime)
            };
            param[0].Value = orgz;
            param[1].Value = start;
            param[2].Value = end;
            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, param))
            {
                while (sdr.Read())
                {
                    list.Add(BuildObjectFromIDataReader(sdr));
                }
                sdr.Close();
            }
            return list;
        }
        /// <summary>
        /// 获取SQL 语句
        /// </summary>
        /// <param name="tag">类型</param>
        /// <returns></returns>
        private string GetSql(int tag)
        {
            string sql = "select COUNT(*) as UseCount,CarNo,CarType,0.0 as Money from VW_CarRecord where BussinessState='false' AND IsComplaint='false' AND OrgzNo=@Orgz And [StartTime]>=@Start AND [StartTime]<=@End group by CarNo,CarType"; //车辆使用次数统计
            switch (tag)
            {
                case 1:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(igmoney) as [Money] from dbo.VW_Car_Illegal where OrgzNo=@Orgz And [IgTime]>=@Start AND [IgTime]<=@End AND InstanceState=3  group by CarNo,CarType"; //违章
                    break;
                case 2:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(MaintenanceMoney) as [Money] from dbo.VW_Car_Maintenance where OrgzNo=@Orgz And [MaintenanceTime]>=@Start AND [MaintenanceTime]<=@End AND InstanceState=3  group by CarNo,CarType";//保养
                    break;
                case 3:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(igmoney) as [Money] from dbo.VW_Car_Accident where OrgzNo=@Orgz And [IgTime]>=@Start AND [IgTime]<=@End AND InstanceState=3  group by CarNo,CarType";//事故
                    break;
                case 4:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(FgMoney) as [Money] from dbo.VW_Car_FillGasoline where OrgzNo=@Orgz And [FgTime]>=@Start AND [FgTime]<=@End AND InstanceState=3  group by CarNo,CarType";//加油
                    break;
                case 5:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(MtMoney) as [Money] from dbo.VW_Car_Maintain where OrgzNo=@Orgz And [MtTime]>=@Start AND [MtTime]<=@End AND InstanceState=3  group by CarNo,CarType";//维修
                    break;
                case 6:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,SUM(InsuranceMoney) as [Money] from dbo.VW_Car_Insurance where OrgzNo=@Orgz And [BeginTime]>=@Start AND [BeginTime]<=@End group by CarNo,CarType";//保险
                    break;
                case 7:
                    sql = "select COUNT(*) as UseCount,CarNo,CarType,0.00 as [Money] from dbo.VW_InsuranceApply where OrgzNo=@Orgz And [ExpTime]>=@Start AND [ExpTime]<=@End group by CarNo,CarType";//保险申请
                    break;
            }
            return sql;
        }
        /// <summary>
        /// 从IDataReader中读取数据，组装成实体
        /// </summary>
        /// <param name="dr">数据读取接口</param>
        /// <returns>Car_Accident实体</returns>
        /// <remarks>
        /// </remarks>
        private ReportModel BuildObjectFromIDataReader(IDataReader dr)
        {
            try
            {
                ReportModel obj = new ReportModel();
                obj.CarType = (dr["CarType"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("CarType")));
                obj.UseCount = (dr["UseCount"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UseCount")));
                obj.Money = (dr["Money"] == System.DBNull.Value ? 0M : dr.GetDecimal(dr.GetOrdinal("Money")));
                obj.CarNo = (dr["CarNo"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("CarNo")));
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception("从DataReader中读取数据失败，可能与相关的字段不存在有关！请检查！||" + ex.Message + "||" + ex.Source);
            }
        }
        /// <summary>
        /// 按照驾驶员统计
        /// </summary>
        /// <param name="orgz">机构</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public IList<ReportModel> GetDriverData(int orgz, DateTime start, DateTime end)
        {
            IList<ReportModel> list = new List<ReportModel>();
            string sql = "Proc_Driver_Count";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@orgz",SqlDbType.Int),
                new SqlParameter("@start",SqlDbType.DateTime),
                new SqlParameter("@end",SqlDbType.DateTime)
            };
            param[0].Value = orgz;
            param[1].Value = start;
            param[2].Value = end;
            using (SqlDataReader dr = DbHelperSQL.RunProcReader(sql, param))
            {
                while (dr.Read())
                {
                    ReportModel obj = new ReportModel();
                    obj.CarType = (dr["Tag"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Tag")));
                    obj.UseCount = (dr["UseCount"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UseCount")));
                    obj.Money = (dr["Money"] == System.DBNull.Value ? 0M : dr.GetDecimal(dr.GetOrdinal("Money")));
                    obj.CarNo = (dr["Name"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Name")));
                    list.Add(obj);
                }
                dr.Close();
            }
            return list;
        }
    }
}
